﻿using Core.Configuration.Config;
using IconYuan.Model.DB;
using Microsoft.AspNetCore.Mvc.ModelBinding.Validation;
using SqlSugar;
using System.Linq.Expressions;
using System.Text;

namespace IconYuan.Manager.DB
{
    public class DbContext<T> where T : class, new()
    {
        //注意：不能写成静态的
        protected SqlSugarClient Db;//用来处理事务多表查询和复杂的操作
        //protected SimpleClient<T> DbClient { get { return new SimpleClient<T>(Db); } }//用来处理t表的常用操作
        public DbContext()
        {
            var dbType = DbType.SqlServer;
            if (GlobalConfig.DbType == "0")
                dbType = DbType.MySql;
            Db = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = GlobalConfig.DbConnectionString,
                DbType = dbType,
                InitKeyType = InitKeyType.Attribute,//从特性读取主键和自增列信息
                IsAutoCloseConnection = true,//开启自动释放模式和EF原理一样我就不多解释了
            });
            WriteSqlLog();
        }

        /// <summary>
        /// SQL日志
        /// </summary>
        protected void WriteSqlLog()
        {
            var str = new StringBuilder();
            Db.Aop.OnLogExecuting = (sql, pars) =>
            {
                str.Append($"SQL: {sql}");
                if (pars != null && pars.Length > 0)
                {
                    foreach (var par in pars)
                    {
                        str.Append($"{par.ParameterName}:{par.Value}");
                    }
                }
            };
        }
    }

    public class BaseContext<T> : DbContext<T> where T : class, new()
    {
        #region 新增数据
        /// <summary>
        ///  写入实体数据
        /// </summary>
        /// <param name="entity">实体数据</param>
        /// <returns></returns>
        public int Insert(T entity)
        {
            return Db.Insertable(entity).ExecuteReturnIdentity();
        }

        /// <summary>
        ///     写入实体数据
        /// </summary>
        /// <param name="entity">实体数据</param>
        /// <returns></returns>
        public async Task<int> InsertAsync(T entity)
        {
            return await Db.Insertable(entity).ExecuteReturnIdentityAsync();
        }

        /// <summary>
        ///  批量写入实体数据
        /// </summary>
        /// <param name="entity">实体类</param>
        /// <returns></returns>
        public int InsertRange(List<T> entity)
        {
            return Db.Insertable(entity.ToArray()).ExecuteReturnIdentity();
        }

        /// <summary>
        ///  批量写入实体数据
        /// </summary>
        /// <param name="entity">实体类</param>
        /// <returns></returns>
        public async Task<int> InsertRangeAsync(List<T> entity)
        {
            return await Db.Insertable(entity.ToArray()).ExecuteCommandAsync();
        }
        #endregion

        #region 删除数据
        /// <summary>
        ///     删除指定ID的数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Delete(object id)
        {
            return Db.Deleteable<T>(id).ExecuteCommandHasChange();
        }

        /// <summary>
        ///  删除数据
        /// </summary>
        /// <param name="where">过滤条件</param>
        /// <returns></returns>
        public bool Delete(Expression<Func<T, bool>> where)
        {
            return Db.Deleteable(where).ExecuteCommandHasChange();
        }

        /// <summary>
        /// 删除指定ID的数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<bool> DeleteAsync(object id)
        {
            return await Db.Deleteable<T>(id).ExecuteCommandHasChangeAsync();
        }

        /// <summary>
        ///  删除数据
        /// </summary>
        /// <param name="where">过滤条件</param>
        /// <returns></returns>
        public async Task<bool> DeleteAsync(Expression<Func<T, bool>> where)
        {
            return await Db.Deleteable(where).ExecuteCommandHasChangeAsync();
        }

        #endregion

        #region 更新数据

        /// <summary>
        ///  更新全字段数据
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool Update(T entity)
        {
            return Db.Updateable(entity).ExecuteCommandHasChange();
        }

        /// <summary>
        ///  更新某个字段
        /// </summary>
        /// <param name="columns">lamdba表达式,如it => new Student() { Name = "a", CreateTime = DateTime.Now }</param>
        /// <param name="where">lamdba判断</param>
        /// <returns></returns>
        public bool Update(Expression<Func<T, T>> columns, Expression<Func<T, bool>> where)
        {
            var i = Db.Updateable<T>().SetColumns(columns).Where(where).ExecuteCommand();
            return i > 0;
        }

        /// <summary>
        ///  手写条件更新数据
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public bool Update(T entity, string strWhere)
        {
            return Db.Updateable(entity).Where(strWhere).ExecuteCommandHasChange();
        }


        /// <summary>
        ///  更新全部字段
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public async Task<bool> UpdateAsync(T entity)
        {
            return await Db.Updateable(entity).ExecuteCommandHasChangeAsync();
        }


        /// <summary>
        ///  更新某个字段
        /// </summary>
        /// <param name="columns">lamdba表达式,如it => new Student() { Name = "a", CreateTime = DateTime.Now }</param>
        /// <param name="where">lamdba判断</param>
        /// <returns></returns>
        public async Task<bool> UpdateAsync(Expression<Func<T, T>> columns, Expression<Func<T, bool>> where)
        {
            return await Db.Updateable<T>().SetColumns(columns).Where(where).ExecuteCommandHasChangeAsync();
        }

        /// <summary>
        ///  手写条件更新全部字段
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public async Task<bool> UpdateAsync(T entity, string strWhere)
        {
            return await Db.Updateable(entity).Where(strWhere).ExecuteCommandHasChangeAsync();
        }

        /// <summary>
        /// 手写sql语句更新数据
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public async Task<bool> UpdateAsync(string sql, SugarParameter[] parameters = null)
        {
            return await Db.Ado.ExecuteCommandAsync(sql, parameters) > 0;
        }

        #endregion

        #region 查询单条数据

        public M GetSingle<M>(Expression<Func<M, bool>> predicate, bool blUseNoLock = false)
        {
            return blUseNoLock
                ? Db.Queryable<M>().WhereIF(predicate != null, predicate).With(SqlWith.NoLock).First()
                : Db.Queryable<M>().WhereIF(predicate != null, predicate).First();
        }

        /// <summary>
        /// 按主值查询单条数据
        /// </summary>
        /// <param name="id">主键值</param>
        /// <param name="blUseNoLock">是否使用WITH(NOLOCK)</param>
        /// <returns>泛型实体</returns>
        public T Get(object id, bool blUseNoLock = false)
        {
            return blUseNoLock
                ? Db.Queryable<T>().With(SqlWith.NoLock).InSingle(id)
                : Db.Queryable<T>().InSingle(id);
        }

        public T Get(Expression<Func<T, bool>> predicate, bool blUseNoLock = false)
        {
            return blUseNoLock
                ? Db.Queryable<T>().WhereIF(predicate != null, predicate).With(SqlWith.NoLock).First()
                : Db.Queryable<T>().WhereIF(predicate != null, predicate).First();
        }

        /// <summary>
        /// 按主值查询单条数据
        /// </summary>
        /// <param name="id">id（必须指定主键特性 [SugarColumn(IsPrimaryKey=true)]），如果是联合主键，请使用Where条件</param>
        /// <param name="blUseNoLock">是否使用WITH(NOLOCK)</param>
        /// <returns>数据实体</returns>
        public async Task<T> GetAsync(object id, bool blUseNoLock = false)
        {
            return blUseNoLock
                ? await Db.Queryable<T>().In(id).With(SqlWith.NoLock).FirstAsync()
                : await Db.Queryable<T>().In(id).FirstAsync();
        }
        public async Task<M> GetAsync<M>(object id, bool blUseNoLock = false)
        {
            return blUseNoLock
                ? await Db.Queryable<M>().In(id).With(SqlWith.NoLock).FirstAsync()
                : await Db.Queryable<M>().In(id).FirstAsync();
        }

        public async Task<M> GetAsync<M>(Expression<Func<M, bool>> predicate, bool blUseNoLock = false)
        {
            return blUseNoLock
               ? await Db.Queryable<M>().WhereIF(predicate != null, predicate).With(SqlWith.NoLock).FirstAsync()
               : await Db.Queryable<M>().WhereIF(predicate != null, predicate).FirstAsync();
        }

        public async Task<T> GetAsync(Expression<Func<T, bool>> predicate, bool blUseNoLock = false)
        {
            return blUseNoLock
               ? await Db.Queryable<T>().WhereIF(predicate != null, predicate).With(SqlWith.NoLock).FirstAsync()
               : await Db.Queryable<T>().WhereIF(predicate != null, predicate).FirstAsync();
        }
        /// <summary>
        /// 指定的主键列表查询
        /// </summary>
        /// <param name="lstIds">id列表（必须指定主键特性 [SugarColumn(IsPrimaryKey=true)]），如果是联合主键，请使用Where条件</param>
        /// <returns>数据实体列表</returns>
        /// <param name="blUseNoLock">是否使用WITH(NOLOCK)</param>
        public List<T> GetByIds(object[] ids, bool blUseNoLock = false)
        {
            return blUseNoLock
                ? Db.Queryable<T>().In(ids).With(SqlWith.NoLock).ToList()
                : Db.Queryable<T>().In(ids).ToList();
        }
        public List<T> GetByIds(int[] ids, bool blUseNoLock = false)
        {
            return blUseNoLock
                ? Db.Queryable<T>().In(ids).With(SqlWith.NoLock).ToList()
                : Db.Queryable<T>().In(ids).ToList();
        }

        /// <summary>
        /// 指定的主键列表查询
        /// </summary>
        /// <param name="lstIds">id列表（必须指定主键特性 [SugarColumn(IsPrimaryKey=true)]），如果是联合主键，请使用Where条件</param>
        /// <returns>数据实体列表</returns>
        /// <param name="blUseNoLock">是否使用WITH(NOLOCK)</param>
        public async Task<List<T>> GetByIdsAsync(object[] lstIds, bool blUseNoLock = false)
        {
            return blUseNoLock
                ? await Db.Queryable<T>().In(lstIds).With(SqlWith.NoLock).ToListAsync()
                : await Db.Queryable<T>().In(lstIds).ToListAsync();
        }
        public async Task<List<T>> GetByIdsAsync(int[] lstIds, bool blUseNoLock = false)
        {
            return blUseNoLock
                ? await Db.Queryable<T>().In(lstIds).With(SqlWith.NoLock).ToListAsync()
                : await Db.Queryable<T>().In(lstIds).ToListAsync();
        }
        #endregion

        #region 查询单表多条数据
        /// <summary>
        ///  根据条件查询数据
        /// </summary>
        /// <param name="strWhere">条件</param>
        /// <param name="orderBy">排序字段，如name asc,age desc</param>
        /// <param name="blUseNoLock">是否使用WITH(NOLOCK)</param>
        public List<T> GetList(string strWhere, string orderBy = "", bool blUseNoLock = false)
        {
            return blUseNoLock
                ? Db.Queryable<T>().OrderByIF(!string.IsNullOrEmpty(orderBy), orderBy)
                    .WhereIF(!string.IsNullOrEmpty(strWhere), strWhere).With(SqlWith.NoLock).ToList()

                : Db.Queryable<T>().OrderByIF(!string.IsNullOrEmpty(orderBy), orderBy)
                    .WhereIF(!string.IsNullOrEmpty(strWhere), strWhere).ToList();
        }

        public List<T> GetList(Expression<Func<T, bool>> predicate, string orderBy = "")
        {
            return Db.Queryable<T>().OrderByIF(!string.IsNullOrEmpty(orderBy), orderBy)
                .WhereIF(predicate != null, predicate).ToList();
        }
        public List<M> GetList<M>(Expression<Func<M, bool>> predicate, string orderBy = "")
        {
            return Db.Queryable<M>().OrderByIF(!string.IsNullOrEmpty(orderBy), orderBy)
                .WhereIF(predicate != null, predicate).ToList();
        }
        /// <summary>
        ///  根据条件查询数据
        /// </summary>
        /// <param name="predicate">条件表达式树</param>
        /// <param name="orderByPredicate">排序字段</param>
        /// <param name="orderByType">排序顺序</param>
        /// <param name="blUseNoLock">是否使用WITH(NOLOCK)</param>
        /// <returns>泛型实体集合</returns>
        public List<T> GetList(Expression<Func<T, bool>> predicate, Expression<Func<T, object>> orderByPredicate, OrderByType orderByType, bool blUseNoLock = false)
        {
            return blUseNoLock
                ? Db.Queryable<T>().OrderByIF(orderByPredicate != null, orderByPredicate, orderByType)
                    .WhereIF(predicate != null, predicate).With(SqlWith.NoLock).ToList()

                : Db.Queryable<T>().OrderByIF(orderByPredicate != null, orderByPredicate, orderByType)
                    .WhereIF(predicate != null, predicate).ToList();
        }

        /// <summary>
        ///  根据条件查询数据
        /// </summary>
        /// <param name="strWhere">条件</param>
        /// <param name="orderBy">排序字段，如name asc,age desc</param>  
        /// <param name="blUseNoLock">是否使用WITH(NOLOCK)</param>
        public async Task<List<T>> GetListAsync(string strWhere, string orderBy = "", bool blUseNoLock = false)
        {
            return blUseNoLock
                ? await Db.Queryable<T>().OrderByIF(!string.IsNullOrEmpty(orderBy), orderBy)
                    .WhereIF(!string.IsNullOrEmpty(strWhere), strWhere).With(SqlWith.NoLock).ToListAsync()

                : await Db.Queryable<T>().OrderByIF(!string.IsNullOrEmpty(orderBy), orderBy)
                    .WhereIF(!string.IsNullOrEmpty(strWhere), strWhere).ToListAsync();
        }

        /// <summary>
        ///     根据条件查询数据
        /// </summary>
        /// <param name="predicate">条件表达式树</param>
        /// <param name="orderBy">排序字段，如name asc,age desc</param>
        /// <returns>泛型实体集合</returns>
        /// <param name="blUseNoLock">是否使用WITH(NOLOCK)</param>
        public async Task<List<T>> GetListAsync(Expression<Func<T, bool>> predicate, string orderBy = "",bool blUseNoLock = false)
        {
            return blUseNoLock
                ? await Db.Queryable<T>().OrderByIF(!string.IsNullOrEmpty(orderBy), orderBy)
                    .WhereIF(predicate != null, predicate).With(SqlWith.NoLock).ToListAsync()

                : await Db.Queryable<T>().OrderByIF(!string.IsNullOrEmpty(orderBy), orderBy)
                    .WhereIF(predicate != null, predicate).ToListAsync();
        }

        public Task<List<M>> GetListAsync<M>(Expression<Func<M, bool>> predicate, string orderBy = "", bool blUseNoLock = false)
        {
            return blUseNoLock
                ? Db.Queryable<M>().OrderByIF(!string.IsNullOrEmpty(orderBy), orderBy)
                    .WhereIF(predicate != null, predicate).With(SqlWith.NoLock).ToListAsync()

                : Db.Queryable<M>().OrderByIF(!string.IsNullOrEmpty(orderBy), orderBy)
                    .WhereIF(predicate != null, predicate).ToListAsync();
        }

        public async Task<List<T>> GetListAsync(Expression<Func<T, bool>> predicate, Expression<Func<T, object>> orderByPredicate, OrderByType orderByType, bool blUseNoLock = false)
        {
            return blUseNoLock
                ? await Db.Queryable<T>().OrderByIF(orderByPredicate != null, orderByPredicate, orderByType)
                    .WhereIF(predicate != null, predicate).With(SqlWith.NoLock).ToListAsync()

                : await Db.Queryable<T>().OrderByIF(orderByPredicate != null, orderByPredicate, orderByType)
                    .WhereIF(predicate != null, predicate).ToListAsync();
        }
        #endregion

        #region 分页查询
        /// <summary>
        ///     根据条件查询分页数据
        /// </summary>
        /// <param name="predicate">判断集合</param>
        /// <param name="orderByType">排序方式</param>
        /// <param name="pageIndex">当前页面索引</param>
        /// <param name="pageSize">分布大小</param>
        /// <param name="orderByExpression"></param>
        /// <param name="blUseNoLock">是否使用WITH(NOLOCK)</param>
        /// <returns></returns>
        public PageList<T> GetPageList(Expression<Func<T, bool>> predicate,
            Expression<Func<T, object>> orderByExpression, OrderByType orderByType, int pageIndex = 1,
            int pageSize = 20, bool blUseNoLock = false)
        {
            var totalCount = 0;
            var page = blUseNoLock
                ? Db.Queryable<T>().OrderByIF(orderByExpression != null, orderByExpression, orderByType)
                    .WhereIF(predicate != null, predicate).With(SqlWith.NoLock)
                    .ToPageList(pageIndex, pageSize, ref totalCount)
                : Db.Queryable<T>().OrderByIF(orderByExpression != null, orderByExpression, orderByType)
                    .WhereIF(predicate != null, predicate).ToPageList(pageIndex, pageSize, ref totalCount);

            return ToPageList(page, pageIndex, pageSize, totalCount);
        }

        /// <summary>
        ///     根据条件查询分页数据
        /// </summary>
        /// <param name="predicate">判断集合</param>
        /// <param name="orderByType">排序方式</param>
        /// <param name="pageIndex">当前页面索引</param>
        /// <param name="pageSize">分布大小</param>
        /// <param name="orderByExpression"></param>
        /// <param name="blUseNoLock">是否使用WITH(NOLOCK)</param>
        /// <returns></returns>
        public async Task<PageList<T>> GetPageListAsync(Expression<Func<T, bool>> predicate, Expression<Func<T, object>> orderByExpression, OrderByType orderByType,
            int pageIndex = 1, int pageSize = 20, bool blUseNoLock = false)
        {
            RefAsync<int> totalCount = 0;
            var page = blUseNoLock
                ? await Db.Queryable<T>().WhereIF(predicate != null, predicate).OrderByIF(orderByExpression != null, orderByExpression, orderByType)
                    .With(SqlWith.NoLock).ToPageListAsync(pageIndex, pageSize, totalCount)
                : await Db.Queryable<T>().WhereIF(predicate != null, predicate).OrderByIF(orderByExpression != null, orderByExpression, orderByType)
                    .ToPageListAsync(pageIndex, pageSize, totalCount);

            return ToPageList(page, pageIndex, pageSize, totalCount);
        }
        public async Task<PageList<M>> GetPageListAsync<M>(Expression<Func<M, bool>> predicate, Expression<Func<M, object>> orderByExpression,
            OrderByType orderByType, int pageIndex = 1, int pageSize = 20, bool blUseNoLock = false) 
        {
            RefAsync<int> totalCount = 0;
            var page = blUseNoLock
                ? await Db.Queryable<M>().WhereIF(predicate != null, predicate).OrderByIF(orderByExpression != null, orderByExpression, orderByType)
                    .With(SqlWith.NoLock).ToPageListAsync(pageIndex, pageSize, totalCount)
                : await Db.Queryable<M>().WhereIF(predicate != null, predicate).OrderByIF(orderByExpression != null, orderByExpression, orderByType)
                    .ToPageListAsync(pageIndex, pageSize, totalCount);

            return ToPageList(page, pageIndex, pageSize, totalCount);
        }
        /// <summary>
        /// 分页查询
        /// </summary>
        public PageList<M> GetPageList<M>(Expression<Func<M, bool>> predicate, string orderBy = "", int pageIndex = 1, int pageSize = 20, bool blUseNoLock = false)
        {
            var totalCount = 0;
            var page = blUseNoLock
                ? Db.Queryable<M>().OrderByIF(!string.IsNullOrEmpty(orderBy), orderBy)
                    .WhereIF(predicate != null, predicate).With(SqlWith.NoLock)
                    .ToPageList(pageIndex, pageSize, ref totalCount)

                : Db.Queryable<M>().OrderByIF(!string.IsNullOrEmpty(orderBy), orderBy)
                    .WhereIF(predicate != null, predicate).ToPageList(pageIndex, pageSize, ref totalCount);

            return ToPageList(page, pageIndex, pageSize, totalCount);
        }
        #endregion

        #region 获取行数
        /// <summary>
        ///  获取数据总数
        /// </summary>
        /// <param name="predicate">条件表达式树</param>
        /// <param name="blUseNoLock">是否使用WITH(NOLOCK)</param>
        /// <returns></returns>
        public int GetCount(Expression<Func<T, bool>> expression, bool blUseNoLock = false)
        {
            return blUseNoLock
                ? Db.Queryable<T>().With(SqlWith.NoLock).Count(expression)
                : Db.Queryable<T>().Count(expression);
        }

        /// <summary>
        ///  获取数据总数
        /// </summary>
        /// <param name="predicate">条件表达式树</param>
        /// <param name="blUseNoLock">是否使用WITH(NOLOCK)</param>
        /// <returns></returns>
        public async Task<int> GetCountAsync(Expression<Func<T, bool>> expression, bool blUseNoLock = false)
        {
            return blUseNoLock
                ? await Db.Queryable<T>().With(SqlWith.NoLock).CountAsync(expression)
                : await Db.Queryable<T>().CountAsync(expression);
        }

        #endregion


        protected PageList<T> ToPageList<T>(List<T> list, int page, int pageSize, int total)
        {
            return new PageList<T>()
            {
                data = list,
                current_page = page,
                per_page = pageSize,
                total = total,
            };
        }
    }
}
